using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using UnityEngine;
using UnityEditor;
using NPOI.Util.Collections;
using NPOI.HPSF;
using UnityEngine.Assertions.Must;
using Array = System.Array;

namespace ExcelSpace
{
    public class SheetInfo
    {
        public string filePath;
        public string fullName;
        public string chineseName;
        public string englishName;
        public string sheetName;

        public string csFilePath;
        const string csFileFolder = "/Scripts/ScriptableObject/";

        public SheetInfo(string path, string a_sheetName = "")
        {
            filePath = Application.dataPath + path.Split("Assets")[1];
            fullName = Path.GetFileNameWithoutExtension(path);
            sheetName = a_sheetName;

            if(path.Contains("#"))
            {
                string[] parts = fullName.Split('#');
                chineseName = parts[0];
                englishName = parts[1];
            }
            else
            {
                chineseName = fullName;
                englishName = fullName;
            }

            csFilePath = Application.dataPath + csFileFolder + englishName + "Sheet.cs";
        }
    }
    
    public class ExcelQuery
    {
        public readonly SheetInfo info = null;
        private readonly IWorkbook workbook = null;
        private readonly ISheet sheet = null;

        public ExcelQuery(string path, string sheetName = "") : this(new SheetInfo(path, sheetName)){}
        public ExcelQuery(SheetInfo a_info)
        {
            try
            {
                info = a_info;

                using (FileStream fileStream = new FileStream(info.filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    workbook = new XSSFWorkbook(fileStream);
                    if(info.sheetName != "")
                    {
                        sheet = workbook.GetSheet(info.sheetName);
                        if(sheet == null)
                        {
                            Debug.LogError($"读取 {info.sheetName} 失败");
                        }
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                        info.sheetName = sheet.SheetName;
                    }
                }
            }
            catch (Exception e)
            {
                Debug.LogError(e.Message);
            }
        }

        public List<string[]> GetTitles()
        {
            List<string[]> result = new List<string[]>();
            IRow title = sheet.GetRow(0);
            IRow type = sheet.GetRow(1);
            for (int i = 0; i < title.LastCellNum; i++)
            {
                ICell cell_title = title.GetCell(i);
                ICell cell_type = type.GetCell(i);
                if (cell_title == null)
                {
                    Debug.LogWarningFormat("Null or empty column is found at {0}.\n", i);
                    continue;
                }

                string value_title = cell_title.StringCellValue;
                string value_type = cell_type.StringCellValue.Replace("[]", "");
                string value_array = cell_type.StringCellValue.Contains("[]") ? "true" : "false";
                result.Add(new string[]{value_title, value_type, value_array});
            }
            return result;
        }

        public void CreateAsset(string targetFolder)
        {
            string targetAssetPath = Path.Combine(targetFolder, info.englishName + "Sheet.asset");

            Type tSheet = Type.GetType($"ExcelSpace.{info.englishName}Sheet, Assembly-CSharp");
            Type tData = Type.GetType($"ExcelSpace.{info.englishName}Data, Assembly-CSharp");

            ScriptableObject sheetInstance = ScriptableObject.CreateInstance(tSheet);
            tSheet.GetField("sheetName").SetValue(sheetInstance, info.englishName);

            List<object> dataInstances = new List<object>();
            FieldInfo[] fileds = tData.GetFields();
            for (int rowIndex = 4; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                object dataInstance = Activator.CreateInstance(tData);
                for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++)
                {
                    ICell cell = row.GetCell(cellIndex);
                    if(cell == null)
                    {
                        continue;
                    }
                    object cellValue = GetCellValue(cell);

                    if(cellIndex >= fileds.Length || cellValue == null)
                    {
                        continue;
                    }
                    Type fieldType = fileds[cellIndex].FieldType;

                    fileds[cellIndex].SetValue(dataInstance, ConvertValue(cellValue, fieldType));
                }
                dataInstances.Add(dataInstance);
            }
            
            Array dataArray = Array.CreateInstance(tData, dataInstances.Count);
            for (int i = 0; i < dataInstances.Count; i++)
            {
                dataArray.SetValue(dataInstances[i], i);
            }
            tSheet.GetField("dataArray").SetValue(sheetInstance, dataArray);

            AssetDatabase.CreateAsset(sheetInstance, targetAssetPath);
        }

        public object GetCellValue(ICell cell)
        {
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue;
                    }
                    else
                    {
                        return cell.NumericCellValue;
                    }
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.String:
                            return cell.StringCellValue;
                        case CellType.Numeric:
                            return cell.NumericCellValue;
                        case CellType.Boolean:
                            return cell.BooleanCellValue;
                        default:
                            return cell.CellFormula;
                    }
                case CellType.Error:
                    return cell.ErrorCellValue;
                case CellType.Blank:
                    return null;
                default:
                    return null;
            }
        }

        public object ConvertValue(object cellValue, Type fieldType)
        {
            object convertedValue;
            if (fieldType.IsEnum)
            {
                // 特殊处理枚举类型的转换
                convertedValue = Enum.Parse(fieldType, cellValue.ToString());
            }
            else if (fieldType.IsArray)
            {
                // 特殊处理任意类型数组的转换
                Type elementType = fieldType.GetElementType();
                string[] stringValues = cellValue.ToString().Split(',');
                Array arrayValue = Array.CreateInstance(elementType, stringValues.Length);

                for (int i = 0; i < stringValues.Length; i++)
                {
                    if(String.IsNullOrEmpty(stringValues[i]))
                    {
                        continue;
                    }
                    object elementValue = Convert.ChangeType(stringValues[i], elementType);
                    arrayValue.SetValue(elementValue, i);
                }

                convertedValue = arrayValue;
            }
            else
            {
                convertedValue = Convert.ChangeType(cellValue, fieldType);
            }
            return convertedValue;
        }
    }
}
